<?php
if (!defined("KU6_PROJECT")) {
	die("hacking attack");
}
require_once dirname(__FILE__) . '/Ku6ErrorCode.php';
require_once dirname(__FILE__) . '/Core.php';
/**
 * DB
 * @author xucognbin
 *
 */
class Ku6AnalyiticsModel
{
    
    private $_conn = null;
    private $errNo = 0;
    private $errMsg = '';
    
    const INVITE_LIMIT = DEFAULT_SUGGEST_NUM;
    
    public function __construct($inMigrate = FALSE)
    {        
        //web 请求不同oracle发生关系
        if ($inMigrate === FALSE) {
            return TRUE;
        }

        $_conn = @oci_pconnect(DB_USER, DB_PASS, DB_HOST.":".DB_PORT."/".DB_NAME, "ZHS16GBK");
        if (!$_conn) {
            $e = oci_error();
            $msg = "[{$e['code']}] {$e['message']}: {$e['sqltext']}";
            $this->setError(Ku6ErrorCode::API_EC_CONNECT_DB, "[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            alert($msg, ALERT_DB_NO);
        }
        $this->_conn = $_conn;
    }
    
    public function __destruct()
    {
        /*
        if ($this->_conn) {
            oci_close($this->_conn);
        }
         */
    }

    /**
     * 迁移oracle数据到redis
     */
    public function migrateDbToRedis()
    {
        /**
         * 0, 清空缓存
         * 1, 迁移ass_rule库
         * 2, 迁移hot_video库
         * 3, 迁移info_map库
         */

        //migrate ass rule
        $table = getTestConfig('InviteVideoTable');
        if (!$table) {
            return FALSE;
        }
        
        $flag = $this->clearCachedVideoLists();
        if ($flag === FALSE) {
            return FALSE;
        }

        $sql = "SELECT conf, lhand, rhand FROM {$table['table']}";
        $st = oci_parse($this->_conn, $sql);
        if ( ! oci_execute($st)) {
            return FALSE;
        }
        while (($row = oci_fetch_array($st, OCI_BOTH))) {
            $key = $row['LHAND'];
            $value = array(
                'VIDEO_ID' => $row['RHAND'],
                'CONF' => $row['CONF'],
                );
            $flag = $this->setCachedVideoListsByVid($key, $value);
            if ($flag === FALSE) {
                return FALSE;
            }
        }
        oci_free_statement($st);

        //migrate hot
        $res = $this->getHotVideos();
        if ($res === FALSE)
            return FALSE;
        $key = 'cache_hot';
        foreach ($res as $row) {
            $value = array(
                'VIDEO_ID' => $row['VIDEO_ID'],
                'CONF' => $row['CONF'],
                );
            $flag = $this->setCachedVideoListsByVid(NULL, $value, TRUE);
            if ($flag === FALSE) {
                return FALSE;
            }
        }
        unset($res);

        //migrate info map
        $res = $this->getIdInfoMap();
        if ($res === FALSE) {
            return FALSE;
        }
		$db = getRedisRWDB();
        $cacher = getCacher($db);
        foreach ($res as $row) {
			$key = 'info_' . $row['VIDEO_ID'];
			$flag = $cacher->kvset($key, $row);
            if ($flag === FALSE) {
                return FALSE;
            }
        }

        return TRUE;
    }

    /**
     * 清空redis缓存
     */
    public function clearCachedVideoLists()
    {
        $db = getRedisRWDB();

        $cacher = getCacher($db);
        $flag = $cacher->flush();
        sleep(5);
        return $flag;
    }

    /**
     * 按照当前观看视频ID获取匹配缓存
     */
    public function & getCachedVideoListsByVid($vid, $hot = FALSE) 
    {
        $db = getRedisRWDB();

        $cacher = getCacher($db);
        if ($hot) {
            if ($hot !== TRUE) {
                $cacheKey = $hot;
            } else {
                $cacheKey = 'cache_hot';
            }
        } else if (is_array($vid)) {
            $cacheKey = $vid;
        } else {
            $cacheKey = getCacheKey($vid);
        }

        $videos = $cacher->get($cacheKey);

        return $videos;
    }

    /**
     * 按照当前观看视频id设置匹配的视频缓存
     */
    public function setCachedVideoListsByVid($vid, $video, $hot = FALSE)
    {
        $db = getRedisRWDB();

        $cacher = getCacher($db);
        if ($hot) {
            if ($hot !== TRUE) {
                $cacheKey = $hot;
            } else {
                $cacheKey = 'cache_hot';
            }
        } else {
            $cacheKey = getCacheKey($vid);
        }

        $flag = $cacher->set($cacheKey, $video);
        return $flag;
    }

    /**
     * 取得视频第一阶段，带缓存处理
     */
    public function getVideosStage1($vid, $ids, $number)
    {
        //$cacheHit = TRUE;
        $videos = & $this->getReferInviteVideosFromNoSql($vid);
        /*
        if ($videos === FALSE) {
            $cacheHit = FALSE;
            $videos = $this->getReferInviteVideos($vid);
        }
         */
        if ($videos === FALSE) {
            return FALSE;
        }

        //extract $number videos from cached data, and alleviate the redundant
        $extracted = array();
        foreach ($videos as $videoId) {
            if ( ! in_array($videoId, $ids)) {
                $extracted[] = $videoId;
            }
            if (sizeof($extracted) == $number)
                break;
        }

        //store videos to cache
        /*
        if ( ! $cacheHit) {
            //$this->setCachedVideoListsByVid($vid, $videos);
        }
         */

        return $extracted;
    }

    /**
     * 取视频第二步, 无缓存
     */
    public function getVideosStage2($ids, $number, $notInIds = array(), $usedInStep1) 
    {
        $rows = $this->getInviteVideosFromNoSql($ids, $usedInStep1);
        if ($rows === FALSE) {
            return FALSE;
        }
        
        //remove NotInIds, and only return $number items
        $notInIds = empty($notInIds) ? $ids : array_merge($ids, $notInIds);
        $extracted = array();
        foreach ($rows as $videoId) {
            if ( ! in_array($videoId, $notInIds)) {
                $extracted[] = $videoId;

                if (sizeof($extracted) == $number) {
                    break;
                }
            }
        }

        return $extracted;
    }

    /**
     * 取视频第三步, 从热门视频表中取, 有缓存
     */
    public function getVideosStage3($limit, $notInIds = array())
    {
        //$cacheHit = TRUE;
        //get hot videos
        $videos = $this->getHotVideosFromNoSql();
        if ($videos === FALSE) {
            return FALSE;
            /*
            $cacheHit = FALSE;
            $videos = $this->getHotVideos();
             */
        }
        /*
        if ($videos === FALSE) {
            return FALSE;
        }
         */

        //extract $number videos from cached data, and alleviate the redundant
        $extracted = array();
        foreach ($videos as $videoId) {
            if ( ! in_array($videoId, $notInIds)) {
                $extracted[] = $videoId;
            }
            if (sizeof($extracted) == $limit)
                break;
        }

        //store videos to cache
        /*
        if ( ! $cacheHit) {
            $this->setCachedVideoListsByVid($vid, $videos, TRUE);
        }
         */

        return $extracted;
    }

    /**
     * 从nosql数据库中取$vid匹配的视频
     */
    public function getReferInviteVideosFromNoSql($vid)
    {
        $key = $vid;

        $res = $this->getCachedVideoListsByVid($vid);
        if ($res === FALSE) {
            return FALSE;
        }
        usort($res, 'sortByConf');
        $videoIds = array();
        foreach ($res as $row) {
            $videoIds[] = $row['VIDEO_ID'];
        }
        unset($res);

        return $videoIds;
    }

    /**
     * 查数据库 精确匹配当前观看视频
     */
    public function getReferInviteVideos($vid)
    {
        $returnLimit = 100;

    	$table = getTestConfig('InviteVideoTable');
    	if (!$table) {
    		$this->setError(Ku6ErrorCode::API_EC_UNKNOWN, "invite table config can not get.");
            return FALSE;
    	}
    	$bindParam = ':rvid';
        $sql = "SELECT A.video_id
        FROM (select {$table['rhand']} as video_id from {$table['table']} 
        where {$table['lhand']} = {$bindParam}";
        $sql.=" ORDER BY {$table['conf']} DESC) 
         A WHERE ROWNUM<= :end";

        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':end', $returnLimit);
        $param = ':rvid';
        oci_bind_by_name($st, $bindParam, $vid);
    	
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $msg = "[{$e['code']}] {$e['message']}: {$e['sqltext']}";
            $this->setError(Ku6ErrorCode::API_EC_SERVICE, $msg);
            alert($msg, ALERT_QUERY_NO);
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, - 1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC) === false) {
            $e = oci_error($st);
            $msg = "[{$e['code']}] {$e['message']}: {$e['sqltext']}";
            $this->setError(Ku6ErrorCode::API_EC_SERVICE, $msg);
            alert($msg, ALERT_QUERY_NO);
            return false;
        }
        return $rows;
    }

    /**
     * 从redis中读 最近观看视频匹配的推荐数据
     */
    public function getInviteVideosFromNoSql($ids, $usedInStep1)
    {
        if (sizeof($ids) == 1 && $ids[0] == $usedInStep1) {
            return array();
        }

        $videoIds = array();

        $inIds = array();
        $rows = $this->buildCondtion($ids);
        foreach ($rows as $key) {
            if ($key != $usedInStep1) {
                $inIds[] = $key;
            }
        }
        $db = getRedisRWDB();
        $cacher = getCacher($db);
        $inCache = $cacher->get($inIds);

        //缓存中无数据
        if (empty($inCache)) {
            return FALSE;
        }

        usort($inCache, 'sortByConf');
        foreach ($inCache as $item) {
            $videoIds[] = $item['VIDEO_ID'];
        }
        $videoIds = array_unique($videoIds);

        return $videoIds;
    }

    /**
     * 查询推荐视频
     * @param array $ids 最近观看的4部视频包括播放区当前播放的视频ID 
     * @param integer $number 数量
     * 
     * @return boolean|aray 
     */
    public function getInviteVideos($ids)
    {
        $returnLimit = 100;

    	if (!$ids) {
            $this->setError(Ku6ErrorCode::API_EC_PARAM, "video ids is empty.");
            return false;
    	}
    	$table = getTestConfig('InviteVideoTable');
    	if (!$table) {
    		$this->setError(Ku6ErrorCode::API_EC_UNKNOWN, "invite table config can not get.");
            return false;
    	}
    	$inIds = $this->buildCondtion($ids);
        $sql = "SELECT A.video_id
        FROM (select {$table['rhand']} as video_id
        from {$table['table']} 
        where {$table['lhand']} in (";
        $count1 = count($inIds);
        for($i=0;$i<$count1; $i++) {
            $sql.=":vid".($i+1).",";
        }
        $sql = substr($sql, 0, -1);
        $sql.=")
        order by {$table['conf']} desc) A WHERE ROWNUM<= :end";

        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':end', $returnLimit);
        for($i=0;$i<$count1; $i++) {
            $param = ':vid'.($i+1);
            oci_bind_by_name($st, $param, $inIds[$i]);
        }
        
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $msg = "[{$e['code']}] {$e['message']}: {$e['sqltext']}";
            $this->setError(Ku6ErrorCode::API_EC_SERVICE, $msg);
            alert($msg, ALERT_QUERY_NO);
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, - 1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC) === false) {
            $e = oci_error($st);
            $msg = "[{$e['code']}] {$e['message']}: {$e['sqltext']}";
            $this->setError(Ku6ErrorCode::API_EC_SERVICE, $msg);
            alert($msg, ALERT_QUERY_NO);
            return false;
        }
        return $rows;
    }

    /**
     * 从redis中获取热门视频列表
     */
    public function getHotVideosFromNoSql()
    {
        $cacheKey = 'cache_hot';
        $res = $this->getCachedVideoListsByVid(NULL, $cacheKey);
        $videoIds = array();
        if ($res === FALSE) {
            return FALSE;
        }
        //usort($res, 'sortByConfAsc');
        foreach ($res as $row) {
            $videoIds[] = $row['VIDEO_ID'];
        }
        $videoIds = array_unique($videoIds);
        shuffle($videoIds);

        return $videoIds;
    }
    
    /**
     * 查询热门视频
     * 
     * @return array|boolean
     */
    public function getHotVideos()
    {
        $table = getTestConfig('HotVideoTable');
    	if (!$table) {
    		$this->setError(Ku6ErrorCode::API_EC_UNKNOWN, "hot table config can not get.");
            return false;
    	} 
        $sql =  "SELECT {$table['video_id']} as video_id, video_rank as CONF
        FROM {$table['table']} ";
        $sql.="order by video_rank DESC";

        $st = oci_parse($this->_conn, $sql);
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $msg = "[{$e['code']}] {$e['message']}: {$e['sqltext']}";
            $this->setError(Ku6ErrorCode::API_EC_SERVICE, $msg);
            alert($msg, ALERT_QUERY_NO);
            return FALSE;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, - 1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC) === FALSE) {
            $e = oci_error($st);
            $msg = "[{$e['code']}] {$e['message']}: {$e['sqltext']}";
            $this->setError(Ku6ErrorCode::API_EC_SERVICE, $msg);
            alert($msg, ALERT_QUERY_NO);
            return FALSE;
        }
        return $rows;
    }

    /**
     * 从NOSQL中获取Id Info Map
     */
    public function getIdInfoMapFromNoSql($keys)
    {
       foreach ($keys as &$key) {
            $key = 'info_' . $key;
        }
        $db = getRedisRWDB();
        $cacher = getCacher($db);
        $ret = $cacher->mget($keys);
        $ret = $ret ? $ret : array();
        foreach ($ret as &$row) {
            $row = unserialize($row);
        }

        return $ret;        
    }

    /**
     * 获得视频ID， IMAGE_URL, URL映射信息
     */
    public function getIdInfoMap()
    {
        $sql =  "SELECT rhand as video_id,
        url, img_url, name,
        channelid, label
        FROM ku6_fid_info_map";

        $st = oci_parse($this->_conn, $sql);
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $msg = "[{$e['code']}] {$e['message']}: {$e['sqltext']}";
            $this->setError(Ku6ErrorCode::API_EC_SERVICE, $msg);
            alert($msg, ALERT_QUERY_NO);
            return FALSE;
        }
        $res = array();
        if (oci_fetch_all($st, $res, 0, - 1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC) === FALSE) {
            $e = oci_error($st);
            $msg = "[{$e['code']}] {$e['message']}: {$e['sqltext']}";
            $this->setError(Ku6ErrorCode::API_EC_SERVICE, $msg);
            alert($msg, ALERT_QUERY_NO);
            return FALSE;
        }
        $rows = array();
        foreach ($res as $row) {
            $rows[$row['VIDEO_ID']] = $row;
        }
        
        return $rows;
    }

    /**
     * 获取推荐视频入口逻辑
     */
    public function getVideos($ids, $number, $vid=null)
    {
        $curId = $vid;
        if (!$number) {
        	$number = self::INVITE_LIMIT;
        }
        $result = array();
        if ($vid) {
            $result = $this->getVideosStage1($vid, $ids, $number);
        	if ($result === FALSE) {
        		return FALSE;
        	}
        }
        $count = count($result);
        if ($count < $number) {
        	$padLength = $number - $count;
            $tmp1 = array();
            foreach($result as $vid) {
                $tmp1[] = $vid;
            }
            if ($ids) {
	            $tmp = $this->getVideosStage2($ids, $padLength, $tmp1, $curId);
	            unset($tmp1);
	            if ($tmp === FALSE) {
	            	return FALSE;
	            }  
	            $result = array_merge($result, $tmp);
	            unset($tmp);  
	        } 
        } 
        $count = count($result);
        if ($count < $number) {
            $padLength = $number-$count;
            $tmp1 = array();
            foreach($result as $vid) {
                $tmp1[] = $vid;
            }
            $tmp1 = array_merge($ids, $tmp1);
            $tmp = $this->getVideosStage3($padLength, $tmp1);
            unset($tmp1);
            if ($tmp === FALSE) {
            	return FALSE;
            }  
            $result = array_merge($result, $tmp);
            unset($tmp);          
        }
        $infoMap = $this->getIdInfoMapFromNoSql($result);

        $tmp = array();
        foreach ($result as $i => $videoId) {
            $url = '';
            $imgUrl = '';
            $name = '';
            $channelId = '';
            $label = '';

            if (isset($infoMap[$i]) && $infoMap[$i]) {
                $url = $infoMap[$i]['URL'];
                $imgUrl = $infoMap[$i]['IMG_URL'];
                $name = $infoMap[$i]['NAME'];
                $channelId = $infoMap[$i]['CHANNELID'];
                $label = $infoMap[$i]['LABEL'];
            }

            $tmp[] = array(
                'video_id' => $videoId,
                'name' => iconv('gbk', 'UTF-8', $name),
                'url' => $url,
                'img_url' => $imgUrl,
                'channel_id' => $channelId,
                'label' => iconv('gbk', 'UTF-8', $label),
                );
		}

        unset($result);
        return $tmp;
    }
    
    private function buildCondtion($vids)
    {
        $glue = "','";
//        $glue = ",";
        $sep = " & ";
        $count = count($vids);
        $sqlArr = array();
    	if($count>3) {
           $id0 = array_shift($vids);
           $tmp = $this->buildCondtion($vids); 
           $sqlArr[] = $id0; 
           foreach($tmp as $val) {
           	$sqlArr[] = $id0.$sep.$val;
           }       
           $sqlArr = array_merge($sqlArr, $tmp);
           return $sqlArr;
        }
        for($i=0; $i<$count; $i++) {
            $tmp = $vids[$i];
//            $tmp = ":vid{$i}";
            $sqlArr[] = $tmp;
            for($j=$i+1; $j<$count; $j++) {
                $tmp.=$sep.$vids[$j];
//                $tmp.=$sep.":vid{$j}";
                $sqlArr[] = $tmp;   
            	if($j-$i>1) {
                    $sqlArr[] = $vids[$i].$sep.$vids[$j];
                }             
            }
        }
//        return join($glue, $sqlArr);
//        return "'".join($glue, $sqlArr)."'";
        return $sqlArr;
    }
    
    /**
     * 记录推荐信息
     */
    public function logExposure($cookid, $vids, $rids)
    {
        $table = getTestConfig('RecomendExposureTable');
    	if (!$table) {
    		$this->setError(Ku6ErrorCode::API_EC_UNKNOWN, "Recomend Exposure Table config can not get.");
            return false;
    	}
    	$fields = $table;
    	unset($fields['table']);
    	$sql = "INSERT INTO {$table['table']} (".join(",", $fields).") values(:userid, :vid1, :vid2, :vid3, :vid4, :rid1, 
    	:rid2, :rid3, :rid4, :rid5, :rid6, :rid7, :rid8, TO_DATE(:rec_time, 'yyyy-mm-dd hh24:mi:ss'), :data_desc)";
    	$rec_time = date("Y-m-d H:i:s");
    	$data_desc = date("Y-m-d");
    	$st = oci_parse($this->_conn, $sql);
    	
    	oci_bind_by_name($st, ':userid', $cookid);
    	oci_bind_by_name($st, ':vid1', $vids[0]);
        for($i=1; $i<4; $i++) {
    	    if (!isset($vids[$i])) {
    	    	$vids[$i] = null;
    	    }
    	    oci_bind_by_name($st, ':vid'.($i+1), $vids[$i]);
    	}
    	for($i=0; $i<8; $i++) {
    	    if (!isset($rids[$i])) {
    	    	$rids[$i] = null;
    	    }
    	    oci_bind_by_name($st, ':rid'.($i+1), $rids[$i]);
    	}
    	oci_bind_by_name($st, ':rec_time', $rec_time);
    	oci_bind_by_name($st, ':data_desc', $data_desc);
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $msg = "[{$e['code']}] {$e['message']}: {$e['sqltext']}";
            $this->setError(Ku6ErrorCode::API_EC_SERVICE, $msg);            
            alert($msg, ALERT_QUERY_NO);
            return false;
        }
        return true;
    }
    
    public function getErrNo ()
    {
        return $this->errNo;
    }
    public function getErrMsg ()
    {
        return $this->errMsg;
    }
    protected function setError ($errNo, $errMsg)
    {
        $this->errNo = $errNo;
        $this->errMsg = $errMsg;
    }
}
